﻿using System;
using System.Xml.Serialization;
using System.Collections.Generic;
using System.Data;
using System.Text;
using XXF.Extensions;
using XXF.Db;
using Dyd.BaseService.ServiceCenter.Domain.Model;

namespace Dyd.BaseService.ServiceCenter.Domain.Dal
{
    //tb_service
    public partial class tb_service_dal
    {
        #region C

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DbConn conn, tb_service model)
        {
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tb_service(");
            strSql.Append("servicename,servicenamespace,servicetype,istest,isautoboostpercent,protocolversion,protocolversionupdatetime,serviceupdatetime,createtime,remark");
            strSql.Append(") values (");
            strSql.Append("@servicename,@servicenamespace,@servicetype,@istest,@isautoboostpercent,@protocolversion,@protocolversionupdatetime,@serviceupdatetime,@createtime,@remark");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            par.Add(new ProcedureParameter("@servicename", model.servicename));
            par.Add(new ProcedureParameter("@servicenamespace", model.servicenamespace));
            par.Add(new ProcedureParameter("@servicetype", model.servicetype));
            par.Add(new ProcedureParameter("@istest", model.istest));
            par.Add(new ProcedureParameter("@isautoboostpercent", model.isautoboostpercent));
            par.Add(new ProcedureParameter("@protocolversion", model.protocolversion));
            par.Add(new ProcedureParameter("@protocolversionupdatetime", model.protocolversionupdatetime));
            par.Add(new ProcedureParameter("@serviceupdatetime", model.serviceupdatetime));
            par.Add(new ProcedureParameter("@createtime", model.createtime));
            par.Add(new ProcedureParameter("@remark", model.remark));


            object obj = conn.ExecuteScalar(strSql.ToString(), par);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }

        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DbConn conn, tb_service model)
        {
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tb_service set ");

            strSql.Append(" servicename = @servicename , ");
            strSql.Append(" servicenamespace = @servicenamespace , ");
            strSql.Append(" servicetype = @servicetype , ");
            strSql.Append(" istest = @istest , ");
            strSql.Append(" isautoboostpercent = @isautoboostpercent , ");
            strSql.Append(" protocolversion = @protocolversion , ");
            strSql.Append(" protocolversionupdatetime = @protocolversionupdatetime , ");
            strSql.Append(" serviceupdatetime = @serviceupdatetime , ");
            strSql.Append(" createtime = @createtime , ");
            strSql.Append(" remark = @remark  ");
            strSql.Append(" where id=@id ");

            par.Add(new ProcedureParameter("@id", model.id));
            par.Add(new ProcedureParameter("@servicename", model.servicename));
            par.Add(new ProcedureParameter("@servicenamespace", model.servicenamespace));
            par.Add(new ProcedureParameter("@servicetype", model.servicetype));
            par.Add(new ProcedureParameter("@istest", model.istest));
            par.Add(new ProcedureParameter("@isautoboostpercent", model.isautoboostpercent));
            par.Add(new ProcedureParameter("@protocolversion", model.protocolversion));
            par.Add(new ProcedureParameter("@protocolversionupdatetime", model.protocolversionupdatetime));
            par.Add(new ProcedureParameter("@serviceupdatetime", model.serviceupdatetime));
            par.Add(new ProcedureParameter("@createtime", model.createtime));
            par.Add(new ProcedureParameter("@remark", model.remark));

            int rows = conn.ExecuteSql(strSql.ToString(), par);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(DbConn conn, int id)
        {
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tb_service ");
            strSql.Append(" where id=@id");
            par.Add(new ProcedureParameter("@id", id));
            int rows = conn.ExecuteSql(strSql.ToString(), par);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Q
        /// <summary>
        /// 重复规则
        /// </summary>
        public bool IsExists(DbConn conn, tb_service model)
        {
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            StringBuilder stringSql = new StringBuilder();
            stringSql.Append(@"select top 1 id from tb_service s where 1=1");

            stringSql.Append(@" and id!=@id");
            par.Add(new ProcedureParameter("@id", model.id));


            stringSql.Append(@" and (servicename=@servicename or servicenamespace=@servicenamespace)");
            par.Add(new ProcedureParameter("@servicename", model.servicename));
            par.Add(new ProcedureParameter("@servicenamespace", model.servicenamespace));

            DataSet ds = new DataSet();
            conn.SqlToDataSet(ds, stringSql.ToString(), par);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public tb_service Get(DbConn conn, int id)
        {
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id, servicename, servicenamespace, servicetype, istest, isautoboostpercent, protocolversion, protocolversionupdatetime, serviceupdatetime, createtime, remark  ");
            strSql.Append(" from tb_service ");
            strSql.Append(" where id=@id");
            par.Add(new ProcedureParameter("@id", id));
            DataSet ds = new DataSet();
            conn.SqlToDataSet(ds, strSql.ToString(), par);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return CreateModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="search">查询实体</param>
        /// <param name="totalCount">总条数</param>
        /// <returns></returns>
        public IList<tb_service> GetPageList(DbConn conn, tb_service_search search, out int totalCount)
        {
            totalCount = 0;
            List<ProcedureParameter> par = new List<ProcedureParameter>();
            IList<tb_service> list = new List<tb_service>();
            long startIndex = (search.Pno - 1) * search.PageSize + 1;
            long endIndex = startIndex + search.PageSize - 1;
            par.Add(new ProcedureParameter("startIndex", startIndex));
            par.Add(new ProcedureParameter("endIndex", endIndex));

            StringBuilder baseSql = new StringBuilder();
            baseSql.Append("select row_number() over(order by id desc) as rownum,");
            #region Query
            string sqlWhere = " where 1=1 ";

            if (!string.IsNullOrWhiteSpace(search.servicename))
            {

            }

            if (search.id > 0)
            {
                sqlWhere += " and id = @id";
                par.Add(new ProcedureParameter("id", ProcParType.Int32, 4, search.id));
            }

            if (!string.IsNullOrWhiteSpace(search.key))
            {
                sqlWhere += " and (id in (select serviceid from tb_protocolversion where protocoljson like '%'+ @key +'%')";
                par.Add(new ProcedureParameter("key", ProcParType.NVarchar, 500, search.key));

                sqlWhere += " or (servicename like '%'+ @servicename +'%' or servicenamespace like  '%'+ @servicenamespace +'%'))";
                par.Add(new ProcedureParameter("servicename", ProcParType.NVarchar, 50, search.key));
                par.Add(new ProcedureParameter("servicenamespace", ProcParType.NVarchar, 50, search.key));
            }

            if (search.servicetype > int.MinValue)
            {
                sqlWhere += " and servicetype = @servicetype";
                par.Add(new ProcedureParameter("servicetype", ProcParType.Int32, 4, search.servicetype));
            }
            #endregion
            baseSql.Append(" * ");
            baseSql.Append(" FROM tb_service with(nolock)");
            string execSql = "select * from(" + baseSql.ToString() + sqlWhere + ")  as s where rownum between @startIndex and @endIndex";
            string countSql = "select count(1) from tb_service with(nolock)" + sqlWhere;
            object obj = conn.ExecuteScalar(countSql, par);
            if (obj != DBNull.Value && obj != null)
            {
                totalCount = LibConvert.ObjToInt(obj);
            }
            DataTable dt = conn.SqlToDataTable(execSql, par);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var model = CreateModel(dr);
                    list.Add(model);
                }
            }
            return list;
        }

        #endregion

        #region private
        public tb_service CreateModel(DataRow dr)
        {
            var model = new tb_service();
            if (dr.Table.Columns.Contains("id") && dr["id"].ToString() != "")
            {
                model.id = int.Parse(dr["id"].ToString());
            }
            if (dr.Table.Columns.Contains("servicename") && dr["servicename"].ToString() != "")
            {
                model.servicename = dr["servicename"].ToString();
            }
            if (dr.Table.Columns.Contains("servicenamespace") && dr["servicenamespace"].ToString() != "")
            {
                model.servicenamespace = dr["servicenamespace"].ToString();
            }
            if (dr.Table.Columns.Contains("servicetype") && dr["servicetype"].ToString() != "")
            {
                model.servicetype = int.Parse(dr["servicetype"].ToString());
            }
            if (dr.Table.Columns.Contains("istest") && dr["istest"].ToString() != "")
            {
                if ((dr["istest"].ToString() == "1") || (dr["istest"].ToString().ToLower() == "true"))
                {
                    model.istest = true;
                }
                else
                {
                    model.istest = false;
                }
            }
            if (dr.Table.Columns.Contains("isautoboostpercent") && dr["isautoboostpercent"].ToString() != "")
            {
                if ((dr["isautoboostpercent"].ToString() == "1") || (dr["isautoboostpercent"].ToString().ToLower() == "true"))
                {
                    model.isautoboostpercent = true;
                }
                else
                {
                    model.isautoboostpercent = false;
                }
            }
            if (dr.Table.Columns.Contains("protocolversion") && dr["protocolversion"].ToString() != "")
            {
                model.protocolversion = int.Parse(dr["protocolversion"].ToString());
            }
            if (dr.Table.Columns.Contains("protocolversionupdatetime") && dr["protocolversionupdatetime"].ToString() != "")
            {
                model.protocolversionupdatetime = DateTime.Parse(dr["protocolversionupdatetime"].ToString());
            }
            if (dr.Table.Columns.Contains("serviceupdatetime") && dr["serviceupdatetime"].ToString() != "")
            {
                model.serviceupdatetime = DateTime.Parse(dr["serviceupdatetime"].ToString());
            }
            if (dr.Table.Columns.Contains("createtime") && dr["createtime"].ToString() != "")
            {
                model.createtime = DateTime.Parse(dr["createtime"].ToString());
            }
            if (dr.Table.Columns.Contains("remark") && dr["remark"].ToString() != "")
            {
                model.remark = dr["remark"].ToString();
            }

            return model;
        }
        #endregion
    }
}